package com.xoa.service.party;


import com.xoa.dao.party.EnHoleBusinessMapper;
import com.xoa.dao.party.MapOwnerinfoMapper;
import com.xoa.model.party.EnHoleBusiness;
import com.xoa.model.users.Users;
import com.xoa.util.DateFormat;
import com.xoa.util.ExcelUtil;
import com.xoa.util.GetAttachmentListUtil;
import com.xoa.util.ToJson;
import com.xoa.util.common.log.FileUtils;
import com.xoa.util.common.session.SessionUtils;
import com.xoa.util.page.PageParams;
import org.apache.commons.beanutils.PropertyUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;

import javax.annotation.Resource;
import javax.imageio.ImageIO;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;
import java.awt.image.BufferedImage;
import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.InputStream;
import java.io.OutputStream;
import java.lang.reflect.InvocationTargetException;
import java.util.*;

/**
 * 开墙打洞（经营）
 * Created by gsb on 2018/1/6.
 */
@Service
public class EnHoleBusinessService {

    @Resource
    private EnHoleBusinessMapper enHoleBusinessMapper;
    @Resource
    private MapOwnerinfoMapper mapOwnerinfoMapper;

    /**
     *
     * 创建作者:   季佳伟
     * 创建日期:   2018-1-6 下午14:26:51
     * 类介绍  :   新建
     * 构造参数:
     **/
    public ToJson<EnHoleBusiness> insertSelective(EnHoleBusiness enHoleBusiness , HttpServletRequest request, String gridLocation){
        ToJson<EnHoleBusiness> json = new ToJson<>();
        try {
            Users user= SessionUtils.getSessionInfo(request.getSession(), Users.class, new Users());
            if(gridLocation!=null){
                enHoleBusiness.setStr1(gridLocation);
            }
            enHoleBusiness.setCreateUser(user.getUserId());
            enHoleBusiness.setCreateTime(DateFormat.getDatestr(new Date()));
            enHoleBusinessMapper.insertSelective(enHoleBusiness);
            json.setFlag(0);
            json.setMsg("新建成功");
        }catch (Exception e){
            e.printStackTrace();
            json.setFlag(1);
            json.setMsg(e.getMessage());
        }
        return json;
    }

    /**
     *
     * 创建作者:   季佳伟
     * 创建日期:   2018-1-6 下午14:28:51
     * 类介绍  :   删除
     * 构造参数:
     **/
    public ToJson<EnHoleBusiness> deleteByPrimaryKey(Integer bid){
        ToJson<EnHoleBusiness> json = new ToJson<>();
        try {
            enHoleBusinessMapper.deleteByPrimaryKey(bid);
            json.setFlag(0);
            json.setMsg("true");
        }catch (Exception e){
            e.printStackTrace();
            json.setFlag(1);
            json.setMsg(e.getMessage());
        }

        return json;
    }
    /**
     *
     * 创建作者:   季佳伟
     * 创建日期:   2018-1-6 下午14:28:51
     * 类介绍  :   修改
     * 构造参数:
     **/
    public ToJson<EnHoleBusiness> updateByPrimaryKeySelective(EnHoleBusiness enHoleBusiness , HttpServletRequest request, String gridLocation) {
        ToJson<EnHoleBusiness> json = new ToJson<>();
        try {
            if(gridLocation!=null){
                enHoleBusiness.setStr1(gridLocation);
            }
            enHoleBusinessMapper.updateByPrimaryKeySelective(enHoleBusiness);
            json.setFlag(0);
            json.setMsg("true");
        }catch (Exception e){
            e.printStackTrace();
            json.setFlag(1);
            json.setMsg(e.getMessage());
        }
        return json;
    }
    /**
     *
     * 创建作者:   季佳伟
     * 创建日期:   2018-1-6 下午14:30:51
     * 类介绍  :   详情
     * 构造参数:
     **/
    public ToJson<EnHoleBusiness> selectById(Integer bid, HttpServletRequest request){
        ToJson<EnHoleBusiness> json = new ToJson<>();
        try {
            String sqlType = "xoa"
                    + (String) request.getSession().getAttribute("loginDateSouse");
            EnHoleBusiness enHoleBusiness = enHoleBusinessMapper.selectById(bid);
            if(enHoleBusiness!=null){
                if (enHoleBusiness.getAttachmentName() != null && enHoleBusiness.getAttachmentId() != null) {
                    enHoleBusiness.setAttachments1(GetAttachmentListUtil.returnAttachment(enHoleBusiness.getAttachmentName(), enHoleBusiness.getAttachmentId(), sqlType, GetAttachmentListUtil.MODULE_parameter));
                }
            }
            json.setObject(enHoleBusiness);
            json.setFlag(0);
            json.setMsg("ok");
        }catch (Exception e){
            e.printStackTrace();
            json.setFlag(1);
            json.setMsg(e.getMessage());
        }
        return json;
    }

    /**
     *
     * 创建作者:   季佳伟
     * 创建日期:   2018-1-6 下午14:35:51
     * 类介绍  :   列表  或 导出 也可条件查询
     * 构造参数:
     *
     */
    public ToJson<EnHoleBusiness> queryList(EnHoleBusiness enHoleBusiness, Integer page, Integer pageSize,
                                            boolean useFlag, HttpServletRequest request, HttpServletResponse response, String export){
        ToJson<EnHoleBusiness> json = new ToJson<>();
        try {
            PageParams pageParams=new PageParams();
            pageParams.setPage(page);
            pageParams.setPageSize(pageSize);
            pageParams.setUseFlag(useFlag);
            Map<String, Object> map = new HashMap<String, Object>();
            if(useFlag){
                map.put("page", pageParams);
            }
            map.put("enHoleBusiness",enHoleBusiness);
            if(export==null){
                export="0";
            }
            List<EnHoleBusiness> enHoleBusinessList = enHoleBusinessMapper.selectList(map);
            if (enHoleBusinessList!=null&&enHoleBusinessList.size()>0){
                for (EnHoleBusiness enHoleBusiness1:enHoleBusinessList){
                    if ("1".equals(enHoleBusiness1.getHouseProperty())){
                        enHoleBusiness1.setHouseProperty("底商");
                    }else if ("2".equals(enHoleBusiness1.getHouseProperty())){
                        enHoleBusiness1.setHouseProperty("居住");
                    }else if ("3".equals(enHoleBusiness1.getHouseProperty())){
                        enHoleBusiness1.setHouseProperty("商住俩用");
                    }else if ("4".equals(enHoleBusiness1.getHouseProperty())){
                        enHoleBusiness1.setHouseProperty("私有");
                    }else if ("5".equals(enHoleBusiness1.getHouseProperty())){
                        enHoleBusiness1.setHouseProperty("住宅");
                    }else {
                        enHoleBusiness1.setHouseProperty("");
                    }
                    if ("1".equals(enHoleBusiness1.getBusinessLicenseStatus())){
                        enHoleBusiness1.setBusinessLicenseStatus("有");
                    }else if ("0".equals(enHoleBusiness1.getBusinessLicenseStatus())){
                        enHoleBusiness1.setBusinessLicenseStatus("无");
                    }else {
                        enHoleBusiness1.setBusinessLicenseStatus("");
                    }
                    if ("1".equals(enHoleBusiness1.getPlaqueSituation())){
                        enHoleBusiness1.setPlaqueSituation("有");
                    }else if ("0".equals(enHoleBusiness1.getPlaqueSituation())){
                        enHoleBusiness1.setPlaqueSituation("无");
                    }else {
                        enHoleBusiness1.setPlaqueSituation("");
                    }
                    if ("1".equals(enHoleBusiness1.getRestoreAppearance())){
                        enHoleBusiness1.setRestoreAppearance("是");
                    }else if ("0".equals(enHoleBusiness1.getRestoreAppearance())){
                        enHoleBusiness1.setRestoreAppearance("否");
                    }else {
                        enHoleBusiness1.setRestoreAppearance("");
                    }
                    if ("1".equals(enHoleBusiness1.getMoveOut())){
                        enHoleBusiness1.setMoveOut("是");
                    }else if ("0".equals(enHoleBusiness1.getMoveOut())){
                        enHoleBusiness1.setMoveOut("否");
                    }else {
                        enHoleBusiness1.setMoveOut("");
                    }
                    String sqlType = "xoa"
                            + (String) request.getSession().getAttribute("loginDateSouse");
                    if(enHoleBusiness1!=null){
                        if (enHoleBusiness1.getAttachmentName() != null && enHoleBusiness1.getAttachmentId() != null) {
                            enHoleBusiness1.setAttachments1(GetAttachmentListUtil.returnAttachment(enHoleBusiness1.getAttachmentName(),
                                    enHoleBusiness1.getAttachmentId(), sqlType, GetAttachmentListUtil.MODULE_parameter));
                        }
                    }

                    String ym = null;
                    StringBuffer sb=new StringBuffer();
                    if (enHoleBusiness1.getAttachments1()!=null && enHoleBusiness1.getAttachments1().size()>0){
                        ym =enHoleBusiness1.getAttachments1().get(0).getYm();
                        sb.append(System.getProperty("file.separator")).append("attach").
                                append(System.getProperty("file.separator")).
                                append(sqlType).append(System.getProperty("file.separator")).
                                append(GetAttachmentListUtil.MODULE_parameter).append(System.getProperty("file.separator")).append(ym);
                        String path =sb.toString();
                        enHoleBusiness1.setFilePath(path);
                    }

                }
            }
            json.setObj(enHoleBusinessList);
            json.setTotleNum(enHoleBusinessMapper.selectCount(enHoleBusiness));
            json.setFlag(0);
            json.setMsg("ok");
            if (export.equals("1")){
                HSSFWorkbook workbook1 = ExcelUtil.makeExcelHead("开墙打洞（经营）信息", 16);
                String[] secondTitles = {"居委会名称", "房屋地址", "房屋性质", "房屋所有者姓名", "房屋所有者身份证号","房屋面积",
                        "房屋用途","营业执照情况","牌匾情况","经营者姓名","经营者身份证号","从业人员人数","经营项目",
                        "是否恢复原貌", "是否已搬离","备注"};
                HSSFWorkbook workbook2 = ExcelUtil.makeSecondHead(workbook1, secondTitles);
                String[] beanProperty = {"committeeName", "homeAddress", "houseProperty", "houseOwnName", "houseOwnCertificate",
                        "houseArea","houseUse","businessLicenseStatus","plaqueSituation","operatorName","operatorId",
                        "employNum","bussinessProject","restoreAppearance","moveOut","note1"};
                HSSFWorkbook workbook = null;
                workbook = ExcelUtil.exportExcelData(workbook2, enHoleBusinessList, beanProperty);
                OutputStream out = response.getOutputStream();
                String filename = "开墙打洞（经营）信息记录表.xls";
                filename = FileUtils.encodeDownloadFilename(filename, request.getHeader("user-agent"));
                response.setContentType("application/vnd.ms-excel;charset=UTF-8");
                response.setHeader("content-disposition", "attachment;filename=" + filename);
                workbook.write(out);
                out.close();

            }
        }catch (Exception e){
            e.printStackTrace();
            json.setFlag(1);
            json.setMsg(e.getMessage());
        }
        return json;

    }

    public static <T> HSSFWorkbook exportExcelData1(HSSFWorkbook workbook, List<T> dataList, String[] beanPropertys) throws IllegalAccessException, InvocationTargetException, NoSuchMethodException {
        HSSFSheet sheet = workbook.getSheetAt(0);
        // 填充数据
        HSSFCellStyle styleData = workbook.createCellStyle();
      //  styleData.setAlignment(HSSFCellStyle.ALIGN_CENTER);
     //   styleData.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);

        HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
        for (int j = 0; j < dataList.size(); j++) {
            HSSFRow rowData = sheet.createRow(j + 2);
            T t = dataList.get(j);
            for(int k=0; k<beanPropertys.length; k++){

                Object value = PropertyUtils.getSimpleProperty(t, beanPropertys[k]);
                // = (String) BeanUtils.getBeanProperty(t, beanPropertys[k].toString());
                HSSFCell cellData = rowData.createCell(k);
                try {
                    if (k ==beanPropertys.length-1){
                        //...user_headImg
                        short h =2;//定义图片所在行
                        short l=2;//定义图片所在列

                        //得到user的图片
                        BufferedImage image=ImageIO.read(new File(value.toString()));
                        ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream();
                        ImageIO.write(image, "jpg", byteArrayOut);
                        HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 0, 0, l, k + h, (short) (l+1), (k+1) + h);
                      //  anchor.setAnchorType(0);
                        // 插入图片
                        patriarch.createPicture(anchor, workbook.addPicture(
                                byteArrayOut.toByteArray(),
                                HSSFWorkbook.PICTURE_TYPE_JPEG));
                    }

                    if (!value.equals("")&&value!=null) {
                        cellData.setCellValue(value.toString());
                    }else {

                        cellData.setCellValue("");
                    }


                    cellData.setCellStyle(styleData);
                } catch (Exception e) {

                }

            }
        }
        return workbook;
    }





    /**
     *
     * 创建作者:   季佳伟
     * 创建日期:   2018-1-6 下午15:35:51
     * 类介绍  :   导入
     * 构造参数:
     *
     */
    public ToJson<EnHoleBusiness> importEnHoleBusiness(MultipartFile file, HttpServletResponse response,
                                                       HttpServletRequest request, HttpSession session) {
        ToJson<EnHoleBusiness> json = new ToJson<>();
        Users users= SessionUtils.getSessionInfo(request.getSession(), Users.class,new Users());
        // 成功次数
        Integer successCount = 0;
        try {

            // 判断是否为空文件
            if (file.isEmpty()) {
                json.setMsg("请上传文件！");
                json.setFlag(1);
                return json;
            }else {
                String fileName = file.getOriginalFilename();
                if (fileName.endsWith(".xls") || fileName.endsWith(".xlsx")) {
                    String uuid = UUID.randomUUID().toString().replaceAll("-", "");
                    int pos = fileName.indexOf(".");//判读当前系统
                    //读取配置文件
                    ResourceBundle rb = ResourceBundle.getBundle("upload");
                    String osName = System.getProperty("os.name");
                    StringBuffer path = new StringBuffer();
                    if (osName.toLowerCase().startsWith("win")) {
                        path = path.append(rb.getString("upload.win"));
                    } else {
                        path = path.append(rb.getString("upload.linux"));
                    }
                    String extName = fileName.substring(pos);
                    String newFileName = uuid + extName;
                    File dest = new File(path.toString(), newFileName);
//                    file.transferTo(dest);
//                    // 将文件上传成功后进行读取文件
//                    // 进行读取的路径
//                    String readPath = path.append(newFileName).toString();
//                    InputStream in = new FileInputStream(readPath);
                    InputStream in =  file.getInputStream();

                    HSSFWorkbook excelObj = new HSSFWorkbook(in);
                    HSSFSheet sheetObj = excelObj.getSheetAt(0);
                    Row row = sheetObj.getRow(0);
                    int colNum = row.getPhysicalNumberOfCells();
                    int lastRowNum = sheetObj.getLastRowNum();
                    EnHoleBusiness enHoleBusiness = null;
                    for (int i = 1; i <= lastRowNum; i++) {
                        enHoleBusiness = new EnHoleBusiness();
                        row = sheetObj.getRow(i);
                        String value = "";
                        if (row != null) {
                            for (int j = 0; j < colNum; j++) {
                                Cell cell = row.getCell(j);
                                if (cell != null) {
                                    switch (j) {
                                        case 0:
                                            //居委会名称
                                            enHoleBusiness.setCommitteeName(cell.getStringCellValue());
                                            break;
                                        case 1:
                                            //房屋地址
                                            enHoleBusiness.setHomeAddress(cell.getStringCellValue());
                                            break;
                                        case 2:
                                            //房屋性质
                                            if ("底商".equals(cell.getStringCellValue().trim())){
                                                enHoleBusiness.setHouseProperty("1");
                                            }else if ("居住".equals(cell.getStringCellValue().trim())){
                                                enHoleBusiness.setHouseProperty("2");
                                            }else if ("商住俩用".equals(cell.getStringCellValue().trim())){
                                                enHoleBusiness.setHouseProperty("3");
                                            }else if ("私有".equals(cell.getStringCellValue().trim())){
                                                enHoleBusiness.setHouseProperty("4");
                                            }else if ("住宅".equals(cell.getStringCellValue().trim())){
                                                enHoleBusiness.setHouseProperty("5");
                                            }else {
                                                enHoleBusiness.setHouseProperty("");
                                            }
                                            break;
                                        case 3:
                                            //房屋所有者姓名
                                            enHoleBusiness.setHouseOwnName(cell.getStringCellValue());
                                            break;
                                        case 4:
                                            //房屋所有者身份证号
                                            if(cell.getCellType() == CellType.NUMERIC){
                                                cell.setCellType(CellType.STRING);
                                            }
                                            enHoleBusiness.setHouseOwnCertificate(cell.getStringCellValue());
                                            break;
                                        case 5:
                                            //房屋面积
                                            if(cell.getCellType() == CellType.NUMERIC){
                                                cell.setCellType(CellType.STRING);
                                            }
                                            enHoleBusiness.setHouseArea(cell.getStringCellValue());
                                            break;
                                        case 6:
                                            //房屋用途
                                            enHoleBusiness.setHouseArea(cell.getStringCellValue());
                                            break;
                                        case 7:
                                            //营业执照情况
                                            if ("有".equals(cell.getStringCellValue())){
                                                enHoleBusiness.setBusinessLicenseStatus("1");
                                            }else if ("无".equals(cell.getStringCellValue())){
                                                enHoleBusiness.setBusinessLicenseStatus("0");
                                            }else {
                                            enHoleBusiness.setBusinessLicenseStatus("");
                                        }
                                            break;
                                        case 8:
                                            //牌匾情况
                                            if ("有".equals(cell.getStringCellValue())){
                                                enHoleBusiness.setPlaqueSituation("1");
                                            }else if ("无".equals(cell.getStringCellValue())){
                                                enHoleBusiness.setPlaqueSituation("0");
                                            }else {
                                            enHoleBusiness.setPlaqueSituation("");
                                        }
                                            break;
                                        case 9:
                                            //经营者姓名
                                            enHoleBusiness.setOperatorName(cell.getStringCellValue());
                                            break;
                                        case 10:
                                            //经营者身份证号
                                            if(cell.getCellType() == CellType.NUMERIC){
                                                cell.setCellType(CellType.STRING);
                                            }
                                            enHoleBusiness.setOperatorId(cell.getStringCellValue());
                                            break;
                                        case 11:
                                            //从业人员人数
                                            if(cell.getCellType() == CellType.NUMERIC){
                                                cell.setCellType(CellType.STRING);
                                            }
                                            enHoleBusiness.setEmployNum(cell.getStringCellValue());
                                            break;
                                        case 12:
                                            //经营项目
                                            enHoleBusiness.setBussinessProject(cell.getStringCellValue());
                                            break;
                                        case 13:
                                            //是否恢复原貌
                                            if ("是".equals(cell.getStringCellValue())){
                                                enHoleBusiness.setRestoreAppearance("1");
                                            }else {
                                                enHoleBusiness.setRestoreAppearance("0");
                                            }
                                            break;
                                        case 14:
                                            //是否已搬离
                                            if ("是".equals(cell.getStringCellValue())){
                                                enHoleBusiness.setMoveOut("1");
                                            }else {
                                                enHoleBusiness.setMoveOut("0");
                                            }
                                            break;
                                        case 15:
                                            //备注
                                            enHoleBusiness.setNote1(cell.getStringCellValue());
                                            break;
                                        case 16:
                                            //位置坐标
                                            enHoleBusiness.setStr1(mapOwnerinfoMapper.selectCoordingbyName(cell.getStringCellValue()));
                                            break;
                                        default:
                                            json.setFlag(0);
                                            json.setMsg("err");
                                            break;
                                    }
                                }

                            }
                            enHoleBusiness.setCreateUser(users.getUserId());
                            enHoleBusiness.setCreateTime(DateFormat.getCurrentTime());
                            //将数据放入数据库
                            int count=enHoleBusinessMapper.insertSelective(enHoleBusiness);
                            if(count>0) {
                                successCount = successCount + count;
                            }
                            dest.delete();
                        }
                    }

                }
            }
            if(successCount>0){
                json.setTotleNum(successCount);
                json.setMsg("ok");
                json.setFlag(0);
                return  json;
            }
        }catch (Exception e){
            e.printStackTrace();
            json.setTotleNum(successCount+1);
            json.setFlag(1);
            json.setMsg(e.getMessage());
        }
        return json;

    }

}
